﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AnOtherBlog.Core.Providers.SQL
{
	/// <summary>
	/// Implementa el acceso a datos para la clase Post
	/// </summary>
	public partial class SQLDataProvider : DataProvider
	{

		#region Implement Abstract Class
		/// <summary>
		/// Obtiene una publicación
		/// </summary>
		/// <param name="id">Identificador de la publicación</param>
		/// <returns>Post</returns>
		public override Post PostSelect(Guid id)
		{
			Post post = null;
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"SELECT "
					+ @"PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Slug, BlogID "
					+ @"FROM aob_Posts "
					+ @"WHERE PostID=@PostID";

					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@PostID", id));

					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							post = SetPostProperties(reader, false);
						}
					}

					if (post != null)
					{
						// Carguemos las categorías asociadas
						command.CommandText = @"SELECT "
						+ @"aob_Categories.CategoryID, aob_Categories.CategoryName "
						+ @"FROM aob_Categories LEFT OUTER JOIN aob_PostCategory ON aob_PostCategory.CategoryID = aob_Categories.CategoryID "
						+ @"WHERE aob_PostCategory.PostID=@PostID";

						command.CommandType = CommandType.Text;
						command.Parameters.Clear();
						command.Parameters.Add(new SqlParameter("@PostID", post.PostID));

						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
							{
								post.Categories.Add(new Category(reader.GetGuid(0), reader.GetString(1), string.Empty));
							}
						}

						// TODO: Cargar las otras relaciones ...
					}
				}
			}
			return post;
		}

		/// <summary>
		/// Inserta una publicación
		/// </summary>
		/// <param name="post">Publicación a insertar</param>
		public override void PostInsert(Post post)
		{
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					// Nos aseguramos que no esté
					//command.CommandText = @"DELETE FROM aob_Posts WHERE PostID=@PostID";
					//command.CommandType = CommandType.Text;
					//command.Parameters.Clear();
					//command.Parameters.Add(new SqlParameter("@PostID", post.PostID));
					//command.ExecuteNonQuery();

					command.CommandText = @"INSERT INTO aob_Posts "
					+ @"( PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Slug, BlogID) VALUES "
					+ @"(@PostID,@Title,@Description,@PostContent,@DateCreated,@DateModified,@Author,@IsPublished,@IsCommentEnabled,@Slug,@BlogID)";

					command.CommandType = CommandType.Text;
					command.Parameters.Clear();

					command.Parameters.Add(new SqlParameter("@PostID", post.PostID));
					command.Parameters.Add(new SqlParameter("@Title", post.Title));
					command.Parameters.Add(new SqlParameter("@Description", post.Description));
					command.Parameters.Add(new SqlParameter("@PostContent", post.PostContent));
					command.Parameters.Add(new SqlParameter("@DateCreated", post.DateCreated));
					command.Parameters.Add(new SqlParameter("@DateModified", post.DateModified));
					command.Parameters.Add(new SqlParameter("@Author", post.Author));
					command.Parameters.Add(new SqlParameter("@IsPublished", post.IsPublished));
					command.Parameters.Add(new SqlParameter("@IsCommentEnabled", post.IsCommentEnabled));
					command.Parameters.Add(new SqlParameter("@Slug", post.Slug));
					command.Parameters.Add(new SqlParameter("@BlogID", Blog.Current.BlogID));

					command.ExecuteNonQuery();

					// Completamos las asociaciones con categorías
					command.CommandText = @"INSERT INTO aob_PostCategory "
					+ @"( PostID, CategoryID) VALUES "
					+ @"(@PostID,@CategoryID)";
					command.CommandType = CommandType.Text;

					foreach (Category category in post.Categories)
					{
						command.Parameters.Clear();
						command.Parameters.Add(new SqlParameter("@PostID", post.PostID));
						command.Parameters.Add(new SqlParameter("@CategoryID", category.CategoryID));

						command.ExecuteNonQuery();
					}

				}
			}
		}

		/// <summary>
		/// Modifica una publicacíon
		/// </summary>
		/// <param name="post">Publicación a modificar</param>
		public override void PostUpdate(Post post)
		{
			// TODO: Esto no me gusta pero por ahora pasa
			this.PostDelete(post);
			this.PostInsert(post);
		}

		/// <summary>
		/// Suprime una publicacíon
		/// </summary>
		/// <param name="page">Publicacíon a suprimir</param>
		public override void PostDelete(Post post)
		{
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"DELETE FROM aob_PostCategory WHERE PostID=@PostID";
					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@PostID", post.PostID));
					command.ExecuteNonQuery();

					command.CommandText = @"DELETE FROM aob_Posts WHERE PostID=@PostID";
					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@PostID", post.PostID));
					command.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Obtiene la lista de todas las publicaciones
		/// </summary>
		/// <returns>Colección de publicaciones</returns>
		public override List<Post> PostList()
		{
			return this.PostList(false);
		}

		/// <summary>
		/// Obtiene la lista de todas las publicaciones
		/// </summary>
		/// <param name="isProxy">Modo de carga true=lectura reducida</param>
		/// <returns>Colección de publicaciones</returns>
		public override List<Post> PostList(bool isProxy)
		{
			List<Post> posts= new List<Post>();
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"SELECT "
					+ @"PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Slug, BlogID "
					+ @"FROM aob_Posts "
					+ @"WHERE BlogID=@BlogID";

					command.CommandType = CommandType.Text;
					command.Parameters.Add(new SqlParameter("@BlogID", Blog.Current.BlogID));

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							posts.Add(SetPostProperties(reader, isProxy));
						}
					}
				}
			}
			return posts;
		}

		#endregion

		#region Utilities Methods

		/// <summary>
		/// Fija todas las propiedades de una publicación
		/// </summary>
		/// <param name="reader">El <see cref="System.Data.SqlClient.SqlDataReader"/>que contiene los datos</param>
		/// <returns>Una nueva <see cref="AnOtherBlog.Core.Post"/> publicación</returns>
		private Post SetPostProperties(SqlDataReader reader)
		{
			return SetPostProperties(reader, false);
		}

		/// <summary>
		/// Fija todas las propiedades de una publicación
		/// </summary>
		/// <param name="reader">El <see cref="System.Data.SqlClient.SqlDataReader"/>que contiene los datos</param>
		/// <param name="isProxy">Lectura reducida</param>
		/// <returns>Una nueva <see cref="AnOtherBlog.Core.Post"/> publicación</returns>
		private Post SetPostProperties(SqlDataReader reader, bool isProxy)
		{
			Post post = new Post();

			if (!reader.IsDBNull(0)) post.PostID = reader.GetGuid(0);
			if (!reader.IsDBNull(1)) post.Title = reader.GetString(1);
			if (!reader.IsDBNull(2)) post.Description = reader.GetString(2);
			if (!isProxy)
			{
				if (!reader.IsDBNull(3)) post.PostContent = reader.GetString(3);
			}
			if (!reader.IsDBNull(4)) post.DateCreated = reader.GetDateTime(4);
			if (!reader.IsDBNull(5)) post.DateModified = reader.GetDateTime(5);
			if (!reader.IsDBNull(6)) post.Author = reader.GetString(6);
			if (!reader.IsDBNull(7)) post.IsPublished = reader.GetBoolean(7);
			if (!reader.IsDBNull(8)) post.IsCommentEnabled = reader.GetBoolean(8);
			if (!reader.IsDBNull(9)) post.Slug = reader.GetString(9);
			if (!reader.IsDBNull(10)) post.BlogID = reader.GetInt32(10);

			post.IsNew = false;
			post.IsProxy = isProxy;
			
			return post;
		}

		#endregion

	}
}
